﻿Imports System
Imports System.Data
Imports System.Windows.Forms
Imports MySql.Data.MySqlClient

Public Class sys_config
    'Inherits System.Windows.Forms.Form
    Dim conn As MySqlConnection
    Dim data As DataTable
    Dim da As MySqlDataAdapter
    Dim cb As MySqlCommandBuilder
    Dim server As String
    Dim userid As String
    Dim password As String
    Dim database As String

    '读取当前信息并显示
    Private Sub sys_config_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim temp() As String
        temp = Split(My.Settings.MysqlDB, ";")
        server = temp(0)
        userid = temp(1)
        password = temp(2)
        database = temp(3)
        temp = Split(server, "=")
        server = temp(1)
        temp = Split(userid, "=")
        userid = temp(1)
        temp = Split(password, "=")
        password = temp(1)
        temp = Split(database, "=")
        database = temp(1)

        Me.TextBox5.Text = server
        Me.TextBox2.Text = userid
        Me.TextBox3.Text = password
        Me.ComboBox1.Text = database

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click



        Dim connStr As String
        connStr = String.Format("server={0};user id={1}; password={2}; database=mysql; pooling=false", _
    TextBox5.Text, TextBox2.Text, TextBox3.Text)

        If Not conn Is Nothing Then conn.Close()
        Try
            conn = New MySqlConnection(connStr)
            conn.Open()
            My.Settings.MysqlDB = connStr
            GetDatabases()
        Catch ex As MySqlException
            MessageBox.Show("Error connecting to the server: " + ex.Message)
        End Try


    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click

        Dim connstr As String
        connstr = String.Format("server={0};user id={1}; password={2}; database={3}; pooling=false", _
    TextBox5.Text, TextBox2.Text, TextBox3.Text, ComboBox1.Text)
        My.Settings.MysqlDB = connstr
        MessageBox.Show("数据库设置成功")

    End Sub

    Private Sub GetDatabases()
        Dim reader As MySqlDataReader
        reader = Nothing
        Dim cmd As New MySqlCommand("SHOW DATABASES", conn)

        If Not conn Is Nothing Then conn.Close()
        conn.Open()
        Try
            reader = cmd.ExecuteReader()
            ComboBox1.Items.Clear()

            While (reader.Read())
                ComboBox1.Items.Add(reader.GetString(0))
            End While
            MessageBox.Show("数据库连接成功")
        Catch ex As MySqlException
            MessageBox.Show("Failed to populate database list: " + ex.Message)
        Finally
            If Not reader Is Nothing Then reader.Close()
        End Try

    End Sub

    Private Sub databaseList_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim cmd As New MySqlCommand("SHOW TABLES", conn)
        Dim connstr As String

        conn.ChangeDatabase(ComboBox1.SelectedItem.ToString())
        connstr = String.Format("server={0};user id={1}; password={2}; database={3}; pooling=false", _
    TextBox5.Text, TextBox2.Text, TextBox3.Text, ComboBox1.Text)
        my.Settings.MysqlDB=connstr

    End Sub

    '退出
    Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click
        Me.Close()
        mainview.Enabled() = True
        mainview.Focus()
    End Sub

    '系统初始化
    Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        Dim connstr As String
        Dim cmd As MySqlCommand
        Dim comm As String

        '创建数据库
        connstr = My.Settings.MysqlDB
        conn = New MySqlConnection(connstr)
        comm = String.Format("CREATE DATABASE GLXT")
        cmd = New MySqlCommand(comm, conn)
        If Not conn Is Nothing Then conn.Close()
        conn.Open()
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try
        conn.Close()

        connstr = String.Format("server={0};user id={1}; password={2}; database=glxt; pooling=false", _
    TextBox5.Text, TextBox2.Text, TextBox3.Text)
        My.Settings.MysqlDB = connstr

        MessageBox.Show("数据库创建完成，点击设置管理员创建管理员", "数据库创建完成")
    End Sub

    '设置管理员
    Private Sub Button7_Click(sender As System.Object, e As System.EventArgs) Handles Button7.Click
        Dim connstr As String
        Dim cmd As MySqlCommand
        Dim comm As String
        Dim username As String
        Dim userpass As String
        Dim userpass1 As String
        Dim userpass2 As String
        Dim usercard As String

        connstr = My.Settings.MysqlDB
        conn = New MySqlConnection(connstr)
        '设置管理员用户名和密码
        username = InputBox("请输入管理员用户名", "创建管理员").ToString
        userpass1 = InputBox("请输入管理员密码", "创建管理员").ToString
        userpass2 = InputBox("请再输入管理员密码", "创建管理员").ToString
        While (userpass1 <> userpass2)
            MessageBox.Show("两次密码不一致，请重新输入", "创建管理员")
            userpass1 = InputBox("请输入管理员密码", "创建管理员").ToString
            userpass2 = InputBox("请再输入管理员密码", "创建管理员").ToString
        End While
        userpass = userpass1
        usercard = InputBox("请刷管理员卡片", "创建管理员").ToString

        '创建用户表
        comm = String.Format("CREATE TABLE USER (num SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,username CHAR(20) NOT NULL ,userpass CHAR(20) NOT NULL ,cardnum INT UNSIGNED NOT NULL ,ygcx TINYINT(1) NOT NULL DEFAULT 0 ,yggl TINYINT(1) NOT NULL DEFAULT 0 ,ddgl TINYINT(1) NOT NULL DEFAULT 0 ,cjgl TINYINT(1) NOT NULL DEFAULT 0 ,scgl TINYINT(1) NOT NULL DEFAULT 0 ,sccx TINYINT(1) NOT NULL DEFAULT 0 ,gjgl TINYINT(1) NOT NULL DEFAULT 0 ,gzjs TINYINT(1) NOT NULL DEFAULT 0 ,PRIMARY KEY (num,cardnum) ,UNIQUE INDEX num_UNIQUE (num ASC))")
        cmd = New MySqlCommand(comm, conn)
        If Not conn Is Nothing Then conn.Close()
        conn.Open()
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try
        conn.Close()

        '创建管理员
        comm = String.Format("INSERT user set username='{0}',userpass='{1}',cardnum='{2}',ygcx='1',yggl='1',ddgl='1',cjgl='1',scgl='1',sccx='1',gjgl='1',gzjs='1'", username.ToString, userpass.ToString, usercard.ToString)
        cmd = New MySqlCommand(comm, conn)
        If Not conn Is Nothing Then conn.Close()
        conn.Open()
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try
        conn.Close()

        '在mysql数据库user表中写入对应的用户

        MessageBox.Show("系统初始化完成，请牢记管理员用户名和密码", "数据库初始化")

        MessageBox.Show("正在创建数据表，请稍后", "数据库初始化")



    End Sub

End Class


